var queues = require('mysql-queues');
const DEBUG = true;
var select = function(connection, callback) {
	var sql = 'select e.id,e.jobNumber,e.workPlace,e.name,e.gender,o.orgName, o.orgAddress, e.entryTime from employee e inner join organization o on e.departmentId=o.id';
	connection.query(sql, function(err, rows) {
		if(err) {
			console.log(err);
			console.log(err.stack);
			}
		callback(rows);
		connection.release();
	});
},
	add = function(connection, data, callback) {
		console.log('con:', data.confirmationDate);
		queues(connection, DEBUG);
		var sql = 'insert into employee(name,gender,idNumber,address,email,' +
			'phone,education,registeredResidence,workPlace,emContactPerson,' +
			'emContactPhone,graduInstitutions,birthDate,residenceType,graduateTime,' +
			'jobNumber,contractNum,job,jobCategory,departmentId,' +
			'state,entryTime,departureTime,dateOfCStart,dateOfCEnd,' +
			'dateOfRenewalStart,dateOfRenewalEnd,bandLevel,currentSalary,salaryLevel,' +
			'bankCardNum,housingFundCard,smInsuranceCard,ifProbation,probationaryPeriod,' +
			'confirmationDate,lastDateOfPromotion,lastDateOfExamination,lastDateOfRise)' +
			'values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
		var getRoleSql = 'select * from role';
		var insertUser = 'insert into user(userName,password,employeeId) values(?,?,?)';
		var insertUserRole = 'insert into user_role(userId, roleId) values(?,?)';
		var trans = connection.startTransaction();
		trans.query(sql, [data.name, data.gender, data.idNumber, data.address, data.email,
			data.phone, data.education, data.registeredResidence, data.workPlace, data.emContactPerson,
			data.emContactPhone, data.graduInstitutions, data.birthDate, data.residenceType, data.graduateTime,
			data.jobNumber, data.contractNum, data.job, data.jobCategory, data.departmentId,
			data.state, data.entryTime, data.departureTime, data.dateOfCStart, data.dateOfCEnd,
			data.dateOfRenewalStart, data.dateOfRenewalEnd, data.bandLevel, data.currentSalary, data.salaryLevel,
			data.bankCardNum, data.housingFundCard, data.smInsuranceCard, data.ifProbation, data.probationaryPeriod,
			data.confirmationDate, data.lastDateOfPromotion, data.lastDateOfExamination, data.lastDateOfRise
		], function(err, rows) {
			console.log('rows',rows);
			var employeeId = rows.insertId;
			var uName = data.email.split('@')[0];
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.query(getRoleSql,function(err, roleData){
					if(err){
						console.log(err);
						console.log(err.stack);
						trans.rollback();
					}
					var role = [];
					for(var i = 0; i < roleData.length; i ++){
						if(roleData[i].roleName == '被考核人'){
							role.push(roleData[i].id);
						}
					}
					trans.query(insertUser, [uName, uName, employeeId], function(err, userData){
						if(err){
							console.log(err);
							console.log(err.stack);
							trans.rollback();
						}
						console.log('userData',userData);
						var uId = userData.insertId;
						trans.query(insertUserRole, [uId, role[0]], function(err, rows){
							if(err){
								console.log(err);
						        console.log(err.stack);
								trans.rollback();
							} else {
								trans.commit();
							}

						});
					});
				});
				//trans.commit();
			}
			// console.log('rows:', rows);
			// console.log('rows[0]', rows[0]);
			callback(rows);
		});
		trans.execute();
		console.log('execute addUserRole');
		connection.release();
	},
	delet = function(connection, id, callback) {
		//删除员工，1删记录信息，2删成绩选项信息，3删成绩信息，4删员工信息，5删用户角色信息，6删用户信息
		//数据库中performance_term表的外键设置了级联删除，所以2步不用程序执行，同理5步不在程序中执行
		queues(connection, DEBUG);
		var recordSql = 'delete from record where employeeId=?';
		//var selectPerformance_term = 'select id from performance where employeeId=?';
		var performanceSql = 'delete from performance where employeeId=?';
		//var performance_termSql = 'delete from performance_term where performanceId=?';
		var employeeSql = 'delete from employee where id=?';
		//var selectSql = 'select id from user where employeeId=?';
		var userSql = 'delete from user where employeeId=?';
		//var userRoleSql = 'delete from user_role where userId=?';
		var trans = connection.startTransaction();
		trans.query(recordSql, id, function(err, rows) {
			if (err)
				trans.rollback();
			else {
				// trans.query(selectPerformance_term, id, function(err, row) {
				// });
				trans.query(performanceSql, id, function(err, rows) {
					if (err) {
						console.log(err);
						console.log(err.stack);
						trans.rollback();
					} else {
						// for (var i = 0; i < row.length; i++) {
						// 	trans.query(performance_termSql, row[i], function(err, rows) {
						// 		if (err) {
						// 			throw err;
						// 			trans.rollback();
						// 		}
						// 	});
						// }
						trans.query(userSql, id, function(err, rows) {
							if (err) {
								console.log(err);
								console.log(err.stack);
								trans.rollback();
							} else {
								trans.query(employeeSql, id, function(err, rows) {
														if (err) {
															console.log(err);
															console.log(err.stack);
															trans.rollback();
														} else {
															trans.commit();
														}
													});
							}
							callback(rows);
						});

						// trans.query(selectSql, id, function(err, rows) {
						// 			if (err) {
						// 				throw err;
						// 				trans.rollback();
						// 			} else {
						// 				if (rows.length == 0) { //如果查询结果为空，表示没有对应user,
						// 					trans.commit();
						// 				} else {
						// 					var userId = rows[0].id;
						// 					console.log('userId');
						// 					trans.query(userRoleSql, userId, function(rows) {
						// 						if (err) {
						// 							throw err;
						// 							trans.rollback();
						// 						} else {
													
						// 						}
						// 					});
						// 				}
						// 			}
						// 		});
					}
				});
			}
		});
		trans.execute();
		console.log('execute');
		connection.release();
	},
	update = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'update employee set name=?,gender=?,idNumber=?,address=?,email=?,' +
			'phone=?,education=?,registeredResidence=?,workPlace=?,emContactPerson=?,' +
			'emContactPhone=?,graduInstitutions=?,birthDate=?,residenceType=?,graduateTime=?,' +
			'jobNumber=?,contractNum=?,job=?,jobCategory=?,departmentId=?,' +
			'state=?,entryTime=?,departureTime=?,dateOfCStart=?,dateOfCEnd=?,' +
			'dateOfRenewalStart=?,dateOfRenewalEnd=?,bandLevel=?,currentSalary=?,salaryLevel=?,' +
			'bankCardNum=?,housingFundCard=?,smInsuranceCard=?,ifProbation=?,probationaryPeriod=?,' +
			'confirmationDate=?,lastDateOfPromotion=?,lastDateOfExamination=?,lastDateOfRise=? where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, [data.name, data.gender, data.idNumber, data.address, data.email,
			data.phone, data.education, data.registeredResidence, data.workPlace, data.emContactPerson,
			data.emContactPhone, data.graduInstitutions, data.birthDate, data.residenceType, data.graduateTime,
			data.jobNumber, data.contractNum, data.job, data.jobCategory, data.departmentId,
			data.state, data.entryTime, data.departureTime, data.dateOfCStart, data.dateOfCEnd,
			data.dateOfRenewalStart, data.dateOfRenewalEnd, data.bandLevel, data.currentSalary, data.salaryLevel,
			data.bankCardNum, data.housingFundCard, data.smInsuranceCard, data.ifProbation, data.probationaryPeriod,
			data.confirmationDate, data.lastDateOfPromotion, data.lastDateOfExamination, data.lastDateOfRise, data.id
		], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log('info:', info);
				});
			}
			console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute update');
		connection.release();
	},
	selEmpByName = function(connection, empName, callback) {
		var sql = "select e.id,e.jobNumber,e.name,e.gender,o.orgName,e.entryTime from employee e left join organization o on e.departmentId=o.id where e.name=?";
		connection.query(sql, empName, function(err, rows) {
			console.log("model---lijuanxia", rows);
			if (err) {
				console.log(err);
				console.log(err.stack);
				callback("err");
			} else {
				callback(rows);
			}

			connection.release();
		});
	},

	findByNameAndEmail = function(connection, data, callback) {
		var sql = 'select * from employee where name=? and email=?';
		connection.query(sql, [data.name, data.email], function(err, rows) {
			callback(rows);
			connection.release();
		});
	},

	findById = function(connection, id, callback) {
		var sql = 'select e.*,o.orgName from employee e inner join organization o on e.departmentId = o.id where e.id=?';
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},

	selEmpByUser = function(connection, empId, callback) {
		var sql = "select id, name, departmentId from employee where id=?";
		connection.query(sql, empId, function(err, rows) {
			callback(rows);
			connection.release();
		});

	},
	//查询有审核权限的员工
	selEmpAss = function(connection, callback) {
		var sql = "select e2.*, o.orgName, o.orgAddress from (select e.* from employee e left join user u2 on e.id = u2.employeeId where u2.id in (select ur.userId from user_role ur left join role r on ur.roleId = r.id where r.roleName = '考核人'))e2 left join organization o on e2.departmentId = o.id";
		connection.query(sql, function(err, rows) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});
	},
	//更加员工姓名查询有审核权限的员工
	selEmpAssByName = function(connection, empName, callback) {
		var sql = "select e2.*, o.orgName, o.orgAddress from (select e.* from employee e left join user u2 on e.id = u2.employeeId where u2.id in (select ur.userId from user_role ur left join role r on ur.roleId = r.id where r.roleName = '考核人'))e2 left join organization o on e2.departmentId = o.id where e2.name = ?";
		connection.query(sql,empName, function(err, rows) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});

	},
	//根据机构id查询用户
	selEmpByOrg = function(connection, orgId, keyword, callback) {
		var sql = "select e2.* from (select e.id,e.jobNumber,e.job, e.name, e.gender, e.departmentId, e.entryTime, o.orgName from employee e left join organization o on e.departmentId = o.id where e.departmentId = ?)e2 where e2.jobNumber like ? or e2.job like ? or e2.name like ?";
		connection.query(sql, [orgId, '%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
		})
	},
	//根据机构ids数组查询用户
	selEmpByOrgIds = function(connection, orgIds, keyword, callback) {
		var sql = "";
		var sqlStr = "(";
		if(orgIds.length != 0) {
			for(var i = 0; i < orgIds.length; i++) {
				if(i < (orgIds.length - 1)){
					sqlStr = sqlStr + orgIds[i] + ",";
				}else{
					sqlStr = sqlStr + orgIds[i] +")";
				}
			}
			sql = "select e2.* from (select e.id,e.jobNumber,e.job, e.name, e.gender, e.departmentId, e.entryTime, o.orgName from employee e left join organization o on e.departmentId = o.id where e.departmentId in " + sqlStr + ")e2 where e2.jobNumber like ? or e2.job like ? or e2.name like ?";
		    connection.query(sql, ['%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%'], function(err, rows) {
		    	if(err){
		    		console.log(err);
		    		console.log(err.stack);
		    		callback(null);
		    	}else{
		    		callback(rows);
		    	}
		    });
		}else{
			callback(null);
		}
		
	},
	selEmpByEntryOrDeparture = function(connection, keyWord, callback) {
		// console.log(keyWord);
		var sql;
		if (keyWord.key==2) {//入职时间
			var sql = "select e.id,e.jobNumber,e.job, e.name, e.gender, e.departmentId, e.entryTime, o.orgName from employee e left join organization o on e.departmentId = o.id where e.entryTime >= '"+ keyWord.from +"' and e.entryTime <='"+keyWord.to+"'";
		} else {//离职时间
			var sql = "select e.id,e.jobNumber,e.job, e.name, e.gender, e.departmentId, e.entryTime, o.orgName from employee e left join organization o on e.departmentId = o.id where e.departureTime >= '"+ keyWord.from +"' and e.departureTime <='"+keyWord.to+"'";
		}
		connection.query(sql, function(err, rows) {
			// console.log(sql);
			if (err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			} else {
				callback(rows);
			}
		})
	},
	searchByKey = function(connection, key, callback) {
		var sql = 'select w.* from (select e.*,o.orgName from employee e left join organization o on e.departmentId = o.id) w where w.name like ? or w.jobNumber like ?';
		connection.query(sql, ['%' + key + '%', '%' + key + '%'], function(err, rows) {
			callback(rows);
			connection.release();
		});
	};

module.exports = {
	selectEmp: select,
	addEmp: add,
	deleteEmp: delet,
	updateEmp: update,
	findById: findById,
	selEmpByName: selEmpByName,
	searchByKey: searchByKey,
	selEmpByUser: selEmpByUser,
	selEmpAss: selEmpAss,
	selEmpByOrg: selEmpByOrg,
	selEmpByEntryOrDeparture: selEmpByEntryOrDeparture,
	selEmpByOrgIds: selEmpByOrgIds,
	selEmpAssByName: selEmpAssByName
};